﻿using lwzlz.Common.NPOI.HSSF.UserModel;
using lwzlz.Common.NPOI.HSSF.Util;
using lwzlz.Common.NPOI.SS.UserModel;
using lwzlz.Common.NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;


namespace lwzlz.Common
{
    public class NpoiExcelHelper
    {
        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataSet">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        /// <param name="tableTitle">表头标题</param>
        public static void ExportToFile(DataSet dataSet, string fileFullPath, string tableTitle = null)
        {
            List<DataTable> dts = new List<DataTable>();
            foreach (DataTable dt in dataSet.Tables) dts.Add(dt);
            ExportToFile(dts, fileFullPath, tableTitle);
        }

        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataTable">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        public static void ExportToFile(DataTable dataTable, string fileFullPath, string tableTitle = null)
        {
            List<DataTable> dts = new List<DataTable>();
            dts.Add(dataTable);
            ExportToFile(dts, fileFullPath, tableTitle);
        }

        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataTables">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        public static void ExportToFile(IEnumerable<DataTable> dataTables, string fileFullPath, string tableTitle = null)
        {

            IWorkbook workbook = new HSSFWorkbook();
            int i = 0;
            foreach (DataTable dt in dataTables)
            {
                string sheetName = string.IsNullOrEmpty(dt.TableName)
                    ? "Sheet " + (++i).ToString()
                    : dt.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);

                int titleRowCount = string.IsNullOrWhiteSpace(tableTitle) ? 0 : 1;

                #region 表格标题
                if (titleRowCount > 0)
                {
                    //标题
                    IRow titleRow = sheet.CreateRow(0);
                    titleRow.HeightInPoints = 25;
                    titleRow.CreateCell(0).SetCellValue(tableTitle);
                    ICellStyle titleStyle = workbook.CreateCellStyle();
                    titleStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                    titleStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中              
                    IFont titleFont = workbook.CreateFont();//定义font
                    titleFont.FontHeightInPoints = 20;
                    titleFont.Boldweight = 700;
                    titleStyle.SetFont(titleFont);
                    titleRow.GetCell(0).CellStyle = titleStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                }
                #endregion

                #region 列头
                //列头样式
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.BorderBottom = BorderStyle.THIN;
                headStyle.BorderLeft = BorderStyle.THIN;
                headStyle.BorderRight = BorderStyle.THIN;
                headStyle.BorderTop = BorderStyle.THIN;
                headStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                headStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                IFont font = workbook.CreateFont();//定义font
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                //列头

                IRow headerRow = sheet.CreateRow(0 + titleRowCount);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
                        ? "Column " + j.ToString()
                        : dt.Columns[j].ColumnName;
                    ICell cell = headerRow.CreateCell(j);
                    cell.SetCellValue(columnName);
                    cell.CellStyle = headStyle;

                }
                #endregion

                #region 内容
                //边框样式
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                //日期样式
                ICellStyle styleDate = workbook.CreateCellStyle();
                styleDate.BorderBottom = BorderStyle.THIN;
                styleDate.BorderLeft = BorderStyle.THIN;
                styleDate.BorderRight = BorderStyle.THIN;
                styleDate.BorderTop = BorderStyle.THIN;
                var format = workbook.CreateDataFormat();
                styleDate.DataFormat = format.GetFormat("yyyy-mm-dd"); ;//格式化显示
                //内容

                for (int a = 0; a < dt.Rows.Count; a++)
                {
                    DataRow dr = dt.Rows[a];
                    IRow row = sheet.CreateRow(a + 1 + titleRowCount);
                    for (int b = 0; b < dt.Columns.Count; b++)
                    {

                        ICell cell = row.CreateCell(b);


                        cell.CellStyle = style;
                        string drValue = dr[b].ToString();

                        switch (dt.Columns[b].DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                cell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = styleDate;
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                cell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }


                    }
                }
                #endregion

                //自动列宽
                for (int j = 0; j <= dt.Columns.Count; j++)
                {
                    sheet.AutoSizeColumn(j, true);
                }
            }

            using (FileStream fs = File.Create(fileFullPath))
            {
                workbook.Write(fs);
            }
        }



        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataTable">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        public static void ExportToFile_salary(DataTable otherSalary, DataTable dataTable, string fileFullPath, string tableTitle = null)
        {
            List<DataTable> dts = new List<DataTable>();
            dts.Add(dataTable);
            ExportToFile_salary(otherSalary, dts, fileFullPath, tableTitle);
        }
        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataTable">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        public static void ExportToFile_salaryT(DataTable otherSalary, DataTable dataTable, string fileFullPath, string tableTitle = null)
        {
            List<DataTable> dts = new List<DataTable>();
            dts.Add(dataTable);
            ExportToFile_salaryT(otherSalary, dts, fileFullPath, tableTitle);
        }
        /// <summary>
        /// 导出Excel文档
        /// </summary>
        /// <param name="dataTables">数据</param>
        /// <param name="fileFullPath">文档路径</param>
        public static void ExportToFile_salary(DataTable otherSalary, IEnumerable<DataTable> dataTables, string fileFullPath, string tableTitle = null)
        {


            IWorkbook workbook = new HSSFWorkbook();
            int i = 0;
            foreach (DataTable dt in dataTables)
            {
                string sheetName = string.IsNullOrEmpty(dt.TableName)
                    ? "Sheet " + (++i).ToString()
                    : dt.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                HSSFPatriarch patr1 = sheet.CreateDrawingPatriarch() as HSSFPatriarch;

                int titleRowCount = string.IsNullOrWhiteSpace(tableTitle) ? 0 : 1;

                #region 表格标题
                if (titleRowCount > 0)
                {
                    //标题
                    IRow titleRow = sheet.CreateRow(0);
                    titleRow.HeightInPoints = 25;
                    titleRow.CreateCell(0).SetCellValue(tableTitle);
                    ICellStyle titleStyle = workbook.CreateCellStyle();
                    titleStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                    titleStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中              
                    IFont titleFont = workbook.CreateFont();//定义font
                    titleFont.FontHeightInPoints = 12;
                    titleFont.Boldweight = 700;
                    titleStyle.SetFont(titleFont);
                    titleRow.GetCell(0).CellStyle = titleStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 2));
                }
                #endregion

                #region 列头
                //列头样式
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.BorderBottom = BorderStyle.THIN;
                headStyle.BorderLeft = BorderStyle.THIN;
                headStyle.BorderRight = BorderStyle.THIN;
                headStyle.BorderTop = BorderStyle.THIN;
                headStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                headStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                IFont font = workbook.CreateFont();//定义font
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                //列头
                IRow headerRow = sheet.CreateRow(0 + titleRowCount);
                for (int j = 1; j < dt.Columns.Count; j++)
                {
                    string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
                        ? "Column " + j.ToString()
                        : dt.Columns[j].ColumnName;
                    ICell cell = headerRow.CreateCell(j - 1);
                    cell.SetCellValue(columnName);
                    cell.CellStyle = headStyle;
                }
                #endregion

                #region 内容
                //边框样式
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                //日期样式
                ICellStyle styleDate = workbook.CreateCellStyle();
                styleDate.BorderBottom = BorderStyle.THIN;
                styleDate.BorderLeft = BorderStyle.THIN;
                styleDate.BorderRight = BorderStyle.THIN;
                styleDate.BorderTop = BorderStyle.THIN;
                var format = workbook.CreateDataFormat();
                styleDate.DataFormat = format.GetFormat("yyyy-mm-dd"); ;//格式化显示
                //内容

                for (int a = 0; a < dt.Rows.Count; a++)
                {
                    DataRow dr = dt.Rows[a];
                    IRow row = sheet.CreateRow(a + 1 + titleRowCount);
                    for (int b = 1; b < dt.Columns.Count; b++)
                    {

                        ICell cell = row.CreateCell(b - 1);
                        #region 创建批注
                        if (a < dt.Rows.Count - 1)
                        {
                            if (b == 13)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 48, cell, a, patr1);

                            }
                            if (b == 14)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 49, cell, a, patr1);

                            }
                            if (b == 17)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 50, cell, a, patr1);
                            }
                        }

                        #endregion

                        cell.CellStyle = style;
                        string drValue = dr[b].ToString();

                        switch (dt.Columns[b].DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                cell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = styleDate;
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                cell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }


                    }
                }
                #endregion


                #region 表格尾部

                ICellStyle footStyle = workbook.CreateCellStyle();
                footStyle.BorderBottom = BorderStyle.NONE;
                footStyle.BorderLeft = BorderStyle.NONE;
                footStyle.BorderRight = BorderStyle.NONE;
                footStyle.BorderTop = BorderStyle.NONE;
                footStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                footStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
                footStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                footStyle.FillForegroundColor = HSSFColor.WHITE.index;

                // IFont font = workbook.CreateFont();//定义font
                // font.FontHeightInPoints = 10;
                //font.Boldweight = 700;
                footStyle.SetFont(font);


                IRow headerFoot = sheet.CreateRow(3 + dt.Rows.Count);
                ICell cell1 = headerFoot.CreateCell(2);
                cell1.SetCellValue("制表：");
                cell1.CellStyle = footStyle;


                ICell cell2 = headerFoot.CreateCell(4);
                cell2.SetCellValue("审核：");
                cell2.CellStyle = footStyle;

                ICell cell3 = headerFoot.CreateCell(6);
                cell3.SetCellValue("审批：");
                cell3.CellStyle = footStyle;

                ICell cell4 = headerFoot.CreateCell(9);
                cell4.SetCellValue("出纳：");
                cell4.CellStyle = footStyle;

                #endregion

                //自动列宽
                for (int j = 0; j <= dt.Columns.Count; j++)
                {
                    sheet.AutoSizeColumn(j, true);
                }
            }

            using (FileStream fs = File.Create(fileFullPath))
            {
                workbook.Write(fs);
            }
        }
        //统计成本
        public static void ExportToFile_salaryCost(DataTable otherSalary, DataSet ds, string fileFullPath, string tableTitle = null)
        {
            List<DataTable> dts = new List<DataTable>();
            foreach (DataTable ddd in ds.Tables)
            {
                dts.Add(ddd);
                ExportToFile_salaryT(otherSalary, dts, fileFullPath, tableTitle);
            }
        }
        public static void ExportToFile_salaryT(DataTable otherSalary, IEnumerable<DataTable> dataTables, string fileFullPath, string tableTitle = null)
        {


            IWorkbook workbook = new HSSFWorkbook();
            int i = 0;
            foreach (DataTable dt in dataTables)
            {
                string sheetName = string.IsNullOrEmpty(dt.TableName)
                    ? "Sheet " + (++i).ToString()
                    : dt.TableName;
                ISheet sheet = workbook.CreateSheet(sheetName);
                HSSFPatriarch patr1 = sheet.CreateDrawingPatriarch() as HSSFPatriarch;

                int titleRowCount = string.IsNullOrWhiteSpace(tableTitle) ? 0 : 1;

                #region 表格标题
                if (titleRowCount > 0)
                {
                    //标题
                    IRow titleRow = sheet.CreateRow(0);
                    titleRow.HeightInPoints = 25;
                    titleRow.CreateCell(0).SetCellValue(tableTitle);
                    ICellStyle titleStyle = workbook.CreateCellStyle();
                    titleStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                    titleStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中              
                    IFont titleFont = workbook.CreateFont();//定义font
                    titleFont.FontHeightInPoints = 12;
                    titleFont.Boldweight = 700;
                    titleStyle.SetFont(titleFont);
                    titleRow.GetCell(0).CellStyle = titleStyle;
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 2));
                }
                #endregion

                #region 列头
                //列头样式
                ICellStyle headStyle = workbook.CreateCellStyle();
                headStyle.BorderBottom = BorderStyle.THIN;
                headStyle.BorderLeft = BorderStyle.THIN;
                headStyle.BorderRight = BorderStyle.THIN;
                headStyle.BorderTop = BorderStyle.THIN;
                headStyle.Alignment = HorizontalAlignment.CENTER;// 左右居中    
                headStyle.VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                IFont font = workbook.CreateFont();//定义font
                font.FontHeightInPoints = 10;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                //列头
                IRow headerRow = sheet.CreateRow(0 + titleRowCount);
                for (int j = 1; j < dt.Columns.Count; j++)
                {
                    string columnName = string.IsNullOrEmpty(dt.Columns[j].ColumnName)
                        ? "Column " + j.ToString()
                        : dt.Columns[j].ColumnName;
                    ICell cell = headerRow.CreateCell(j - 1);
                    cell.SetCellValue(columnName);
                    cell.CellStyle = headStyle;
                }
                #endregion

                #region 内容
                //边框样式
                ICellStyle style = workbook.CreateCellStyle();
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                //日期样式
                ICellStyle styleDate = workbook.CreateCellStyle();
                styleDate.BorderBottom = BorderStyle.THIN;
                styleDate.BorderLeft = BorderStyle.THIN;
                styleDate.BorderRight = BorderStyle.THIN;
                styleDate.BorderTop = BorderStyle.THIN;
                var format = workbook.CreateDataFormat();
                styleDate.DataFormat = format.GetFormat("yyyy-mm-dd"); ;//格式化显示
                //内容

                for (int a = 0; a < dt.Rows.Count; a++)
                {
                    DataRow dr = dt.Rows[a];
                    IRow row = sheet.CreateRow(a + 1 + titleRowCount);
                    for (int b = 1; b < dt.Columns.Count; b++)
                    {

                        ICell cell = row.CreateCell(b - 1);
                        #region 创建批注
                        if (a < dt.Rows.Count - 1)
                        {
                            if (b == 13)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 48, cell, a, patr1);

                            }
                            if (b == 14)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 49, cell, a, patr1);

                            }
                            if (b == 17)
                            {
                                getpostil(otherSalary, dt.Rows[a][0].ToString(), 50, cell, a, patr1);
                            }
                        }

                        #endregion

                        cell.CellStyle = style;
                        string drValue = dr[b].ToString();

                        switch (dt.Columns[b].DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                cell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                cell.SetCellValue(dateV);
                                cell.CellStyle = styleDate;
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                cell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                cell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                cell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理
                                cell.SetCellValue("");
                                break;
                            default:
                                cell.SetCellValue("");
                                break;
                        }


                    }
                }
                #endregion




                //自动列宽
                for (int j = 0; j <= dt.Columns.Count; j++)
                {
                    sheet.AutoSizeColumn(j, true);
                }
            }

            using (FileStream fs = File.Create(fileFullPath))
            {
                workbook.Write(fs);
            }
        }


        /// <summary>
        /// 读取excel到datatable
        /// </summary>
        /// <param name="xlsxFile"></param>
        /// <returns></returns>
        public static List<DataTable> GetDataTablesFrom(string xlsxFile)
        {
            if (!File.Exists(xlsxFile))
                throw new FileNotFoundException("文件不存在");

            List<DataTable> result = new List<DataTable>();
            Stream stream = new MemoryStream(File.ReadAllBytes(xlsxFile));
            IWorkbook workbook = new HSSFWorkbook(stream);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                DataTable dt = new DataTable();
                ISheet sheet = workbook.GetSheetAt(i);
                IRow headerRow = sheet.GetRow(0);

                int cellCount = headerRow.LastCellNum;
                for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(j).StringCellValue);
                    dt.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;
                for (int a = (sheet.FirstRowNum + 1); a < rowCount; a++)
                {
                    IRow row = sheet.GetRow(a);
                    if (row == null) continue;

                    DataRow dr = dt.NewRow();
                    for (int b = row.FirstCellNum; b < cellCount; b++)
                    {
                        if (row.GetCell(b) == null) continue;
                        dr[b] = row.GetCell(b).ToString();
                    }

                    dt.Rows.Add(dr);
                }
                result.Add(dt);
            }
            stream.Close();

            return result;
        }

        public static DataTable GetNewDataTable(DataTable dt, string condition, string orderby)
        {
            DataTable newdt = new DataTable();
            newdt = dt.Clone();
            DataRow[] dr = dt.Select(condition, orderby);
            for (int i = 0; i < dr.Length; i++)
            {
                newdt.ImportRow((DataRow)dr[i]);
            }
            return newdt;//返回的查询结果
        }
        public static void getpostil(DataTable DTotherSalary, string userid, int classid, ICell cell, int a, HSSFPatriarch patr1)
        {

            string ret = "";
            if (DTotherSalary.Rows.Count == 0) return;
            try
            {
                DataTable DT = GetNewDataTable(DTotherSalary, "userid=" + userid + " and salarytypeid=" + classid, "");
                //   comment.String = new HSSFRichTextString("迟到：5元 日期:2017-10-11 说明：家里有有事\n\r迟到：5元 日期:2017-10-11 说明：家里有有事" + a);
                //
                string split = "";
                foreach (DataRow dr in DT.Rows)
                {
                    ret += split + string.Format("{0}：{1}元 日期:{2} 备注：{3}", dr["salaryname"].ToString(), dr["salarymoney"].ToString(), Convert.ToDateTime(dr["salarytime"].ToString()).ToString("yyyy-MM-dd"), dr["remark"].ToString());
                    split = "\n\r";
                }
                if (ret.Trim().Length > 0)
                {

                    HSSFComment comment = patr1.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 5 + a, 1, 12 + a, 10)) as HSSFComment;
                    comment.String = new HSSFRichTextString(ret);
                    comment.Visible = false;
                    cell.CellComment = comment;
                }

            }
            catch (Exception ee)
            {
                string ees = ee.Message.ToString();
            }

            return;
        }
    }
}
